from osv import osv, fields

class kdvn_budget_data(osv.osv):
    _name = "kdvn.budget.data"
    _description="KDVN Budget Data"
    
    def write(self, cr, uid, ids, vals, context=None):
        if ids:
            kbd_ids = ",".join(map(str,ids))
            cr.execute("Select id from kdvn_budget_data kbd where id in (%s) and budget_id<>%d  limit 1" % (kbd_ids,vals['budget_id']))
            if cr.fetchall():
                cr.execute("Select \
                            * \
                        from \
                            kdvn_budget_data kbd\
                        left join \
                            product_product pp on kbd.budget_id = kdvn_budget_id\
                        left join \
                            purchase_order_line pol on kbd.project_id = pol.project_id and pp.id = pol.product_id\
                        where coalesce(pol.id,0)>0 and kbd.id in (%s) limit 1" % (kbd_ids))
                if cr.fetchall():
                    raise osv.except_osv("Error","Can not change budget because P.O., Expense is exist!")
        osv.osv.write(self, cr, uid, ids,vals,context=context)
        return True

    def unlink(self, cr, uid, ids, context=None):
        if ids:
            kbd_ids = ",".join(map(str,ids))
            cr.execute("Select \
                            * \
                        from \
                            kdvn_budget_data kbd\
                        left join \
                            product_product pp on kbd.budget_id = kdvn_budget_id\
                        left join \
                            purchase_order_line pol on kbd.project_id = pol.project_id and pp.id = pol.product_id\
                        where coalesce(pol.id,0)>0 and kbd.id in (%s) limit 1" % (kbd_ids))
            if cr.fetchall():
                raise osv.except_osv("Error","Can not delete budget(s) because P.O., Expense is exist!")
                unlink_ids = []
            else:
                unlink_ids = ids
        return super(kdvn_budget_data, self).unlink(cr, uid, unlink_ids, context=context)
       
    def _get_budget_data_info(self, cr, uid, ids, name, args, context=None):
        res={}
        if ids:
            budget_ids=",".join(map(str,ids))
            budget_list_temp={
                          'order_company_cur':0.0,
                          'payment_company_cur':0.0,
                          'over':False}
            for id in ids:
                res[id] = budget_list_temp
            cr.execute("""Select
                            kbd.id,
                            coalesce(sum(expense_amount),0) as order_company_cur,
                            coalesce(sum(paid_amount),0) as payment_company_cur,
                            case when ((budget_amount-coalesce(sum(expense_amount),0)))<0 then True else False end as over
                        from
                            kdvn_budget_data kbd
                        left join
                            vwexpense_summary vws on kbd.project_id = vws.project_id and kbd.budget_id = vws.budget_id
                        where kbd.id in (%s)
                        group by 
                            budget_amount,kbd.id""" % budget_ids)
            for budget_list in cr.dictfetchall():
                res[budget_list.pop('id')] =budget_list        
            return res
        
    def _get_orders_tmp(self, cr, uid, ids, prop, args, context=None):
        res={}

        if ids:
            for xid in ids:
                res[xid] = []
            conditional = "typeoforder not in (3,5)"
            if prop=='advance_ids':
                conditional = "typeoforder=3"
            elif prop=='other_expense_ids': 
                conditional = "typeoforder=5"
            kbd_ids = ",".join(map(str,ids))
            cr.execute("Select\
                            kbd.id as kdb_id,\
                            vwpol.id as po_id\
                        from\
                            kdvn_budget_data kbd\
                        left join\
                            (Select \
                                po.id,kdvn_budget_id,po.project_id\
                            from\
                                purchase_order po\
                            left join \
                                purchase_order_line pol on po.id = pol.order_id\
                            left join\
                                product_product pp on  product_id=pp.id\
                            where \
                                (%s) and PO.state not in ('draft','cancel') group by po.id,po.project_id,kdvn_budget_id) vwpol on kbd.project_id=vwpol.project_id and budget_id=vwpol.kdvn_budget_id\
                        where kbd.id in (%s)" % (conditional,kbd_ids)) # po.name similar to kp.code || '%'\
            for kbd_id,po_id in cr.fetchall():
                if po_id:
                    res[kbd_id].append(po_id)
        return res

    def _get_orders(self, cr, uid, ids, prop, args, context=None):
        res={}
        if ids:
            for xid in ids:
                res[xid] = []
            kbd_ids = ",".join(map(str,ids))
            cr.execute("""Select 
                                kbd.id,
                                kpfbs.id 
                            from 
                                kdvn_budget_data kbd left join kderp_purchase_for_budget_summary kpfbs on kbd.budget_id = kpfbs.budget_id and kbd.project_id =kpfbs.project_id
                            where kbd.id in (%s) """ % (kbd_ids)) # po.name similar to kp.code || '%'\
            for kbd_id,adv_id in cr.fetchall():
                if adv_id:
                    res[kbd_id].append(adv_id)
        return res
    
    def _get_advs(self, cr, uid, ids, prop, args, context=None):
        res={}
        if ids:
            for xid in ids:
                res[xid] = []
            kbd_ids = ",".join(map(str,ids))
            cr.execute("""Select 
                                kbd.id,
                                kafbs.id 
                            from 
                                kdvn_budget_data kbd 
                            left join 
                                kderp_advance_for_budget_summary kafbs on kbd.budget_id = kafbs.budget_id and kbd.project_id =kafbs.project_id  
                            where kbd.id in (%s) """ % (kbd_ids)) # po.name similar to kp.code || '%'\
            for kbd_id,adv_id in cr.fetchall():
                if adv_id:
                    res[kbd_id].append(adv_id)
        return res

    def _get_other_exps(self, cr, uid, ids, prop, args, context=None):
        res={}
        if ids:
            for xid in ids:
                res[xid] = []
            kbd_ids = ",".join(map(str,ids))
            cr.execute("""Select 
                                kbd.id,
                                oe.id 
                            from 
                                kdvn_budget_data kbd left join kderp_other_expense_for_budget_summary oe on kbd.budget_id = oe.budget_id and kbd.project_id =oe.project_id
                             where kbd.id in (%s) """ % (kbd_ids)) # po.name similar to kp.code || '%'\
            for kbd_id,adv_id in cr.fetchall():
                if adv_id:
                    res[kbd_id].append(adv_id)
        return res

    def _get_payments(self, cr, uid, ids, prop, unknow_none,unknow_dict):
        res={}
        if ids:
            for xid in ids:
                res[xid] = []
            kbd_ids = ",".join(map(str,ids))
            cr.execute("Select\
                            kbd.id as kdb_id,\
                            coalesce(vw_krop.id) as krop_id\
                        from\
                            kdvn_budget_data kbd\
                        left join\
                            (Select \
                                krop.id,\
                                krop.project_id,\
                                krop.budget_id\
                            from\
                                kdvn_request_of_payment krop\
                            where\
                                krop.state not in ('draft','cancel')) vw_krop on kbd.project_id = vw_krop.project_id and kbd.budget_id = vw_krop.budget_id\
                        where kbd.id in (%s)" % kbd_ids) # po.name similar to kp.code || '%'\
            for kbd_id,kropd_id in cr.fetchall():
                if kropd_id:
                    res[kbd_id].append(kropd_id)
        return res

    def _get_actual_budget_amount(self, cr, uid, ids, name, args, context=None):
        res={}
        if ids:
            budget_list=",".join(map(str,ids))
            cr.execute("Select \
                            kbd.id,budget_amount*received_rate as actual_budget_amount\
                        from\
                            kdvn_budget_data kbd\
                        left join\
                            kdvn_project kp on kbd.project_id=kp.id\
                        left join\
                            (Select\
                                id,\
                                case when sum(plan_cost_vnd+plan_vat_vnd) is null or sum(plan_cost_vnd+plan_vat_vnd)=0 then 1 else\
                                sum(contract_amount_vnd + contract_vat_vnd)/sum(plan_cost_vnd+plan_vat_vnd) end as received_rate\
                            from vwproject_contract_received_summary \
                            group by id) vwtemp on kp.contract_id = vwtemp.id\
                            where kbd.id in (%s)" % budget_list)
            for id,current_vnd in cr.fetchall():
                res[id]=current_vnd
        return res
    
    def _calculate_budget_amountusd(self, cr, uid, ids, name, args, context=None):
        res={}
        if ids:
            acc_set = ",".join(map(str, ids))
            cr.execute("Select \
                            kbd.id,\
                            case when rate =0 \
                            then 0 else coalesce(budget_amount/rate,0) end\
                            as budget_amountusd\
                        from \
                            kdvn_budget_data kbd\
                        left join\
                            kdvn_project kp on kbd.project_id=kp.id\
                        left join\
                            project_currency pc on kp.contract_id=pc.contract_id and upper(pc.name)='USD'\
                        where kbd.id in (%s);" % acc_set)
            for id,amountusd in cr.fetchall():
                res[id]=amountusd
        return res

    def _get_budget_code(self, cr, uid, ids, name, args, context={}):
        res = {}
        if ids:
            for id in ids:
                res[id]=0
            kdb_ids = ",".join(map(str,ids))
            cr.execute("Select \
                            kbd.id,\
                            code\
                        from\
                            kdvn_budget_data kbd\
                        left join\
                            account_budget_post abp on kbd.budget_id = abp.id where kbd.id in (%s)" % kdb_ids)
            kdvn_budget_lists = cr.fetchall()
            for kbd_id,code in kdvn_budget_lists:
                    res[kbd_id] = code 
        return res


    _order ="budget_code"
    
    _columns= {
                'budget_code':fields.function(_get_budget_code,type='char',size=10,method=True,string='Budget Code',
                                       store={'kdvn.budget.data': (lambda self, cr, uid, ids, c={}: ids, ['budget_id'], 10)}),
                'budget_id':fields.many2one('account.budget.post','Budget', required=True),
                'project_id':fields.many2one('kdvn.project','Project'),
                'budget_amount':fields.float('Budget Amount', digits=(16,2)),
#                #'actual_budget_amount':fields.function(_get_budget_data_info,
#                                                       type='float',digits=(16,2),
#                                                       string='Current VND',
#                                                       method=True,multi='budget_cal'),
#                'budget_amount_usd':fields.function(_get_budget_data_info,
#                                                       type='float',digits=(16,2),
#                                                       string='USD',
#                                                       method=True,multi='budget_cal'),
                'order_company_cur':fields.function(_get_budget_data_info,
                                                       type='float',digits=(16,2),
                                                       string='Purchased',
                                                       method=True,multi='budget_cal'),
#                'order_usd':fields.function(_get_budget_data_info,
#                                                       type='float',digits=(16,2),
#                                                       string='USD',
#                                                       method=True,multi='budget_cal'),
                'payment_company_cur':fields.function(_get_budget_data_info,
                                                       type='float',digits=(16,2),
                                                       string='Payment',
                                                       method=True,multi='budget_cal'),
                'over':fields.function(_get_budget_data_info,
                                                       type='boolean',
                                                       string='Over',
                                                       method=True,multi='budget_cal'),
               
                'order_ids':fields.function(_get_orders,type='one2many',relation='kderp.purchase.for.budget.summary',method=True,string='PO',link=0),
                #'payment_detail_ids':fields.function(_get_payments,type='one2many',relation='kdvn.request.of.payment',method=True,string='Payment',link=0),
                
                'advance_ids':fields.function(_get_advs,type='one2many',relation='kderp.advance.for.budget.summary',method=True,string='Advance',link=0),
                'other_expense_ids':fields.function(_get_other_exps,type='one2many',relation='kderp.other.expense.for.budget.summary',method=True,string='Other Expense',link=0),                
                'expense_ids':fields.one2many('kderp.budget.expense.detail','kbd_id','Expense Detail'),
#                'actual_budget_amount':fields.function(_get_actual_budget_amount,
#                                                       type='float',digits=(16,2),
#                                                       method=True,string='Current VND'),
#                'budget_amount_usd':fields.function(_calculate_budget_amountusd,type='float',method=True,string="USD")                          
                }
    _sql_constraints = [('code_unique_budget_project', 'unique (budget_id,project_id)', 'KDERP Error: The Budget of this project must be unique !')] 
    _defaults = {  
        'budget_amount':lambda *x:0.0,
        }    
kdvn_budget_data()


class kdvn_total_budget_cat(osv.osv):
    _name = 'kdvn.total.budget.cat'
    _description='KDVN Total Budget Check Input'
    _rec_name="project_id"
    _auto=False
    _columns={
         "project_id":fields.many2one('kdvn.project','Project'),
         "material":fields.float("Material"),
         "sub_contractor":fields.float('Sub-Contractor'),
         "site_expense":fields.float("Site-Expense"),
         "indirect_cost":fields.float("Indirect Cost"),
         "labour":fields.float("Labour"),
         "business_profit":fields.float("Business Profit"),
         "total":fields.float("Total")
        }
    def init(self,cr):
        cr.execute("""Create or replace view kdvn_total_budget_cat as 
                        Select 
                            id,
                            id as project_id,
                            sum(material) as material,
                            sum(sub_contractor) as sub_contractor,
                            sum(site_expense) as site_expense,
                            sum(labour) as labour,
                            sum(indirect_cost) as indirect_cost,
                            sum(business_profit) as business_profit,
                            sum(material+site_expense+sub_contractor+labour+indirect_cost+business_profit) as total
                        from
                            (Select
                                project_id as id,
                                sum(coalesce(budget_amount,0)) as material,
                                0 as sub_contractor,
                                0 as site_expense,
                                0 as labour,
                                0 as indirect_cost,
                                0 as business_profit
                            from 
                                kdvn_budget_data kbd
                            left join
                                kdvn_project kp on kbd.project_id=kp.id
                            left join
                                account_budget_post abp on kbd.budget_id = abp.id
                            left join
                                budget_category bc on abp.categ_id = bc.id
                            where
                                cat_code='material'
                            group by cat_code,project_id
                            union
                            Select 
                                project_id as id,
                                0 as material,
                                sum(coalesce(budget_amount,0)) as sub_contractor,
                                0 as site_expense,
                                0 as labour,
                                0 as indirect_cost,
                                0 as business_profit
                            from 
                                kdvn_budget_data kbd
                            left join
                                kdvn_project kp on kbd.project_id=kp.id
                            left join
                                account_budget_post abp on kbd.budget_id = abp.id
                            left join
                                budget_category bc on abp.categ_id = bc.id
                            where
                                cat_code='sub_contractor'
                            group by cat_code,project_id
                            union
                            Select 
                                project_id as id,
                                0 as material,
                                0 as sub_contractor,
                                sum(coalesce(budget_amount,0)) as site_expense,
                                0 as labour,
                                0 as indirect_cost,
                                0 as business_profit
                            from 
                                kdvn_budget_data kbd
                            left join
                                kdvn_project kp on kbd.project_id=kp.id
                            left join
                                account_budget_post abp on kbd.budget_id = abp.id
                            left join
                                budget_category bc on abp.categ_id = bc.id
                            where
                                cat_code='site_expense'
                            group by cat_code,project_id
                            union
                            Select 
                                project_id as id,
                                0 as material,
                                0 as sub_contractor,
                                0 as site_expense,
                                sum(coalesce(budget_amount,0)) as labour,
                                0 as indirect_cost,
                                0 as business_profit
                            from 
                                kdvn_budget_data kbd
                            left join
                                kdvn_project kp on kbd.project_id=kp.id
                            left join
                                account_budget_post abp on kbd.budget_id = abp.id
                            left join
                                budget_category bc on abp.categ_id = bc.id
                            where
                                cat_code='labour'
                            group by cat_code,project_id
                            union
                            Select 
                                project_id as id,
                                0 as material,
                                0 as sub_contractor,
                                0 as site_expense,
                                0 as labour,
                                coalesce(prj_budget_total,0)*coalesce(auto_rate,0)/100.0 as indirect_cost,
                                coalesce(prj_budget_total,0)-coalesce(prj_budget_total,0)*coalesce(auto_rate,0)/100.0 -sum(coalesce(budget_amount,0)) as business_profit
                            from 
                                budget_category bc,kdvn_project kp
                            left join
                                kdvn_budget_data kbd on kp.id = kbd.project_id
                            where
                                cat_code='indirect_cost'
                            group by cat_code,project_id,prj_budget_total,auto_rate
                            ) vwtemp group by id""")        
kdvn_total_budget_cat()

class kdvn_project(osv.osv):
    _name = "kdvn.project"
    _inherit = "kdvn.project"
  
    _columns={
             'budget_total_cat_ids':fields.one2many('kdvn.total.budget.cat','project_id','Budget Category Total'),
             }
kdvn_project()

class kderp_budget_expense_detail(osv.osv):
    _name = 'kderp.budget.expense.detail'
    _description='KDERP Budget Expense Detail'
    _auto=False
    _rec_name = "kbd_id"
    _columns={
         "currency_id":fields.many2one("kderp.currency",'Currency'),
         "supplier_id":fields.many2one("res.partner",'Partner'),
         "kbd_id":fields.many2one('kdvn.budget.data','Budget Detail'),
         "final_amount":fields.float("Purchase Amt."),
         "expense_amount":fields.float("Expense Amt. In Company Curr."),
         "paid_amount":fields.float("Paid Amt. In Company Curr."),
         "expense_no":fields.text('Exp. No.'),
         "decription":fields.text('Description')
        }
    def init(self,cr):
        cr.execute("""Create or replace view kderp_budget_expense_detail as
                        Select
                            ROW_NUMBER() OVER (ORDER BY kbd_id,expense_no) AS id,
                            kbd_id,
                            project_id,
                            budget_id,
                            expense_no,
                            supplier_id,
                            decription,
                            currency_id,
                            final_amount,
                            expense_amount,
                            paid_amount
                        from
                            (Select --Purchase
                                kbd.id as kbd_id,
                                kbd.project_id,
                                kbd.budget_id,
                                expense_no,
                                supplier_id,
                                decription,
                                currency_id,
                                sum(amount) as final_amount,
                                sum(fncompute(currency_id,get_default_currency(),date_order,'tax_office')*amount) as expense_amount,
                                sum(case when coalesce(total_amount,0)=0 or fncompute(currency_id,get_default_currency(),date_order,'tax_office')=0 then 0 else coalesce(request_amount/(total_amount*fncompute(currency_id,get_default_currency(),date_order,'tax_office')),0) end*fncompute(currency_id,get_default_currency(),date_order,'tax_office')*amount) as paid_amount    
                            from
                                (Select 
                                    total_amount,
                                    request_amount,
                                    po.name as expense_no,
                                    partner_id as supplier_id,
                                    po.notes as decription,
                                    currency_id,
                                    date_order,
                                    po.project_id,
                                    kdvn_budget_id as budget_id,
                                    case when 
                                        coalesce(po.amount_untaxed,0)=0 then 0 
                                    else sum(price_subtotal)-discount*sum(price_subtotal)/po.amount_untaxed end as amount
                                from 
                                    purchase_order po
                                left join
                                    purchase_order_line pol on po.id = order_id
                                left join
                                    product_product pp on product_id = pp.id
                                where
                                    state not in ('draft','cancel') and coalesce(kdvn_budget_id,0)>0
                                group by
                                    po.id,
                                    po.project_id,
                                    kdvn_budget_id) vwpo_budget
                            left join
                                kdvn_budget_data kbd on vwpo_budget.project_id = kbd.project_id and vwpo_budget.budget_id = kbd.budget_id
                            group by
                                kbd.id,
                                expense_no,
                                decription,
                                currency_id,
                                supplier_id
                            Union all
                        
                                --Advance Payment
                                -- Advanced and Not Yet Released
                            Select
                                kbd.id as kbd_id,
                                kbd.project_id,
                                kbd.budget_id,
                                expense_no,
                                null as supplier_id,
                                description,
                                currency_id,
                                sum(final_amount) as final_amount,
                                sum(advanced_amount) as expense_amount,
                                sum(paid_amount) as paid_amount
                            from
                                (Select
                                    kap.project_id,
                                    budget_id,
                                    kap.name as expense_no,
                                    kap.notes as description,
                                    currency_id,
                                    sum(kapl.amount) as final_amount,
                                    sum(fncompute(currency_id,get_default_currency(),date_order,'tax_office')*kapl.amount) as advanced_amount,
                                    sum(fncompute(currency_id,get_default_currency(),date_order,'tax_office')*kapl.amount) as paid_amount
                                from
                                    kderp_advance_payment kap
                                left join
                                    kderp_advance_payment_line kapl on kap.id = adv_id
                                where
                                    state not in ('draft','cancel') and
                                    not exists(Select id from kderp_detail_of_advance kdoa where kdoa.adv_id= kap.id)
                                    and coalesce(budget_id,0)>0
                                Group by
                                    kap.project_id,
                                    budget_id,kap.id
                                union all
                                Select
                                    kap.project_id,
                                    budget_id,
                                    kap.name as expense_no,
                                    kap.notes as description,
                                    currency_id,
                                    sum(kdoa.amount) as final_amount,
                                    sum(fncompute(currency_id,get_default_currency(),date_order,'tax_office')*kdoa.amount) as advanced_amount,
                                    sum(fncompute(currency_id,get_default_currency(),date_order,'tax_office')*kdoa.amount) as paid_amount
                                from
                                    kderp_advance_payment kap
                                left join
                                    kderp_detail_of_advance kdoa on kap.id = kdoa.adv_id
                                where
                                    state not in ('draft','cancel') and kdoa.id >0
                                Group by
                                    kap.project_id,
                                    budget_id,kap.id) vwcombine_advanced
                            left join
                                kdvn_budget_data kbd on vwcombine_advanced.project_id = kbd.project_id and vwcombine_advanced.budget_id = kbd.budget_id        
                            group by
                                kbd.id,
                                expense_no,
                                description,
                                currency_id
                        
                            Union all
                                --Other Expense Area
                            Select
                                kbd.id as kbd_id,
                                kbd.project_id,
                                kbd.budget_id,
                                koe.name as expense_no,
                                null as supplier_id,
                                description,
                                currency_id,
                                sum(coalesce(amount,0)) as final_amount,
                                sum(fncompute(currency_id,get_default_currency(),date,'tax_office')*coalesce(amount,0)) as expense_amount,
                                sum(fncompute(currency_id,get_default_currency(),date,'tax_office')*coalesce(amount,0)) as paid_amount
                            from
                                kderp_other_expense koe
                            left join
                                kderp_other_expense_line koel on koe.id = other_expense_id
                            left join
                                kdvn_budget_data kbd on koe.project_id = kbd.project_id and koel.budget_id = kbd.budget_id        
                            where
                                state not in ('draft','cancel') and coalesce(kbd.id,0)>0
                            Group by
                                kbd.id,
                                koe.id) vwcombined""")
kderp_budget_expense_detail()